
------------------------------------------------------------------------------------
--    TABLE NAME : dbo.ServerOS
--   DESCRIPTION : The ServerOS table contains information about the physical or 
--                 virtual server hosting MS SQL server.  Data in this table is 
--                 collected via PowerShell by executing Upsert-ServerOS.ps1
------------------------------------------------------------------------------------
--  POPULATED BY : Upsert-ServerOS.ps1
--   PROJECT NAME: iSQLPS
--DATA RETENTION : Infinite
------------------------------------------------------------------------------------
-- CHANGE HISTORY:
-- DATE        MODIFIED   DESCRIPTION   
------------------------------------------------------------------------------------
-- 08.08.2010  SYoung     Initial creation.
------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ServerOS](
       [ServerOSID] [int] IDENTITY(100000,1) NOT NULL,
       [HostName] [varchar](128) NOT NULL,
       [Region] [char](2) NOT NULL,
       [location] [char](2) NOT NULL,
       [Description] [varchar](500) NULL,
       [PrimaryBU] [varchar](128) NOT NULL,
       [TimeZone] [varchar](128) NULL,
       [EnableDaylightSavingsTime] [bit] NULL,
       [Domain] [varchar](128) NULL,
       [Manufacturer] [varchar](128) NULL,
       [Model] [varchar](128) NULL,
       [SystemType] [varchar](128) NULL,
       [SystemStartupOptions] [varchar](128) NULL,
       [ProcessorPhysical] [tinyint] NULL,
       [ProcessorLogical] [tinyint] NULL,
       [ProcessorModel] [varchar](128) NULL,
       [TotalPhysicalMemory] [bigint] NULL,
       [CountryCode] [varchar](128) NULL,
       [LastBootUpTime] [smalldatetime] NULL,
       [Locale] [varchar](128) NULL,
       [OS] [varchar](128) NULL,
       [Version] [varchar](128) NULL,
       [ServicePackMajorVersion] [varchar](10) NULL,
       [ServicePackMinorVersion] [varchar](10) NULL,
       [BuildNumber] [varchar](20) NULL,
       [InstallDate] [datetime] NULL,
       [TotalVisibleMemorySize] [bigint] NULL,
       [TotalVirtualMemorySize] [bigint] NULL,
       [PagingFileSize] [bigint] NULL,
       [IP1] [varchar](128) NULL,
       [IP2] [varchar](128) NULL,
       [IP3] [varchar](128) NULL,
       [IP4] [varchar](128) NULL,
       [IP5] [varchar](128) NULL,
       [IP6] [varchar](128) NULL,
       [CreateDate] [smalldatetime] NOT NULL,
       [UpdateDate] [smalldatetime] NOT NULL,
       [Active] [tinyint] NULL,
CONSTRAINT [PK_ServerOS] PRIMARY KEY CLUSTERED (
       [ServerOSID] ASC),
CONSTRAINT [IX_ServerOS_HostName] UNIQUE NONCLUSTERED (
	   [HostName] ASC)
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID of the ServerOS and the primary key.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'ServerOSID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the SQL Server host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'HostName'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Region in which the SQL Server host is located (eg., NA, EU, and AS)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'Region'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Code of the city in which the SQL host is located (eg., NY for New York, CH for Chicago).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'location'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Description of purposes of the SQL Server host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'Description'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary business unit that owns the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'PrimaryBU'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Standard time zone of the host' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'TimeZone'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates if daylight saving time (DST) is enabled on the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'EnableDaylightSavingsTime'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Domain of the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'Domain'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the host''s computer manufacturer (eg., Dell, HP)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'Manufacturer'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Product name that the manufacturer gives to the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'Model'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'System running on the Windows-based computer (eg., X86-based PC, 64-bit Intel PC).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'SystemType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'List of the options for starting up the computer system running Windows.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'SystemStartupOptions'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of logical processors available on the computer.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'ProcessorPhysical'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of physical processors available on the computer.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'ProcessorLogical'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Total size of physical memory.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'TotalPhysicalMemory'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Country code that the host uses.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'CountryCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the host was last restarted.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'LastBootUpTime'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Language identifier used by the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'Locale'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Operating system used by the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'OS'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Version number of the operating system.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'Version'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Major version number of the service pack of the OS installed on the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'ServicePackMajorVersion'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Minor version number of the service pack of the OS installed on the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'ServicePackMinorVersion'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Operating system build number.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'BuildNumber'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'When the host was first built or rebuilt.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'InstallDate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number, in kilobytes, of physical memory available to the operating system. This value does not necessarily indicate the true amount of physical memory, but what is reported to the operating system as available to it.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'TotalVisibleMemorySize'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number, in kilobytes, of virtual memory.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'TotalVirtualMemorySize'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Total number of kilobytes that can be stored in the operating system paging files - 0 indicates that there are no paging files.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'PagingFileSize'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IP Address 1 used by the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'IP1'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IP Address 2 used by the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'IP2'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IP Address 3 used by the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'IP3'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IP Address 4 used by the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'IP4'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IP Address 5 used by the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'IP5'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IP Address 6 used by the host.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'IP6'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the ServerOS record was created.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'CreateDate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the ServerOS record was last updated.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'UpdateDate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'On/Off switch to know if the row should be included in queries.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS', @level2type=N'COLUMN',@level2name=N'Active'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The ServerOS table contains information about the physical or virtual server hosting MS SQL server.  Data in this table is collected via PowerShell by executing Upsert-ServerOS.ps1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerOS'

ALTER TABLE [dbo].[ServerOS] ADD  CONSTRAINT [DF_Host_Active]  DEFAULT ((1)) FOR [Active]
GO